if exists (select 1 from sysobjects where name = 'get_personeninabteilung' and type = 'P')
begin
   drop procedure get_personeninabteilung
   print 'Procedure: get_personeninabteilung deleted ...'
end
go
create procedure get_personeninabteilung(
  @abteilungid            int = 1
)
as
begin
  set nocount on

   create table #prs(
       schemaname    varchar(32),
       schemaid      int,
       prsschemaid   int,
       prsid         int,
       vorname       varchar(32),
       nachname      varchar(32),
       abteilungid   int,
       abteilungname varchar(32),
       rgb           int
   )
      
   insert #prs
   select 
          schmaname     = '',
          schemaid      = 0,
          prsschemaid   = 0,
          prsid         = 0,
          vorname       = Name,
          nachname      = '',
          abteilungid   = AbteilungID,
          abteilungname = Name,
          rgb           = -1
     from Abteilung
    where Abteilung.AbteilungID = @abteilungid
    
   insert #prs
   select schemaname    = '',
          schemaid      = 0,
          prsschemaid   = 1,
          prsid         = isnull(p.PrsId,0),
          vorname       = isnull(Vorname,''),
          nachname      = isnull(Nachname,''),
          abteilungid   = a.AbteilungID,
          abteilungname = a.Name,
          rgb           = -1
      from Abteilung a
     inner join PersonalAbteilung pa
        on pa.AbteilungID = a.AbteilungID
     inner join Person p
        on p.PrsId = pa.PrsId
     
     where a.AbteilungID = @abteilungid
       and pa.Von <= GETDATE()
       and pa.Bis >= GETDATE()
       and p.PersonTyp = 1

   select * 
     from #prs
    order by abteilungid, prsschemaid, prsid

end
go
print 'Procedure: get_personeninabteilung done ...'
go

grant exec on get_personeninabteilung to prsadmins with grant option
go
grant exec on get_personeninabteilung to prsusers
go

